﻿Imports System.Math
Imports DataGridViewAutoFilter
Imports System.Data.SqlClient

Public Class CasesForm

    Dim ChangesMade As Boolean = False
    Dim DefaultRegion As String
    Dim FireChanges As Boolean = False
    Dim ShowMenu As Boolean = True
    Dim CellFormat As String = ""
    Dim IsFullScreen As Boolean = False
    Dim IsFactors As Boolean = False


    Public Sub PrepareCasesReport(ByVal ReportType As Integer)
        'Report Types
        '0 - All Cases
        '1 - Approved Cases
        '2 - Rejected Cases
        '3 - Draft Cases


        Dim Doit As Boolean = False

        If DetailGrid.RowCount = 0 Then Exit Sub

        'first delete all recs

        Dim r As Integer
        Dim rn As Integer
        Dim nStart As Integer
        Dim sql As String
        Dim notesql As String
        Dim NoteView As DataView

        sql = "delete from Cases_Report where User_Name = '" & CurrentUser.Name & "'"

        RunNonQuery(sql)

        For r = 0 To DetailGrid.RowCount - 1

            For c = 0 To DetailGrid.Columns.Count - 1

                If DetailGrid.Columns(c).Name = "Factors" Or DetailGrid.Columns(c).Name = "Learners" Or DetailGrid.Columns(c).Name = "Other" Or DetailGrid.Columns(c).Name = "Rates" Or DetailGrid.Columns(c).Name = "SLNs" Or DetailGrid.Columns(c).Name = "Funding" Then
                    'check if it is not null
                    'all cases
                    Doit = False

                    If Not IsDBNull(DetailGrid.Item(c, r).Value) Then

                        If ReportType = 0 Then
                            Doit = True
                        Else
                            If ReportType = 1 Then
                                'check if this is an approved case
                                If DetailGrid.Item(DetailGrid.Columns(c).Name & "_Status", r).Value.ToString.Contains("Approved") Then
                                    Doit = True
                                Else
                                    Doit = False
                                End If
                            Else
                                If ReportType = 2 Then
                                    'rejected cases
                                    If DetailGrid.Item(DetailGrid.Columns(c).Name & "_Status", r).Value.ToString.Contains("Rejected") Then
                                        Doit = True
                                    Else
                                        Doit = False
                                    End If
                                Else
                                    If ReportType = 3 Then
                                        'draft cases
                                        If DetailGrid.Item(DetailGrid.Columns(c).Name & "_Status", r).Value.ToString.Contains("Rejected") Or DetailGrid.Item(DetailGrid.Columns(c).Name & "_Status", r).Value.ToString.Contains("Approved") Then
                                            Doit = False
                                        Else
                                            Doit = True
                                        End If
                                    Else
                                        Doit = False
                                    End If

                                End If
                            End If
                        End If

                        If Doit = True Then

                            notesql = "select * from Case_Notes where DataCut_ID = '" & TemplateGrid.Item(TemplateGrid.Columns("DataCut_ID").Index, TemplateGrid.CurrentRow.Index).Value.ToString & "' and RID = '" & DetailGrid.Item(DetailGrid.Columns("RID").Index, r).Value.ToString & "'"

                            NoteView = LoadDataView(notesql)

                            nStart = 0
                            If NoteView.Count = 0 Then nStart = -1

                            For rn = nStart To NoteView.Count - 1

                                sql = "INSERT INTO Cases_Report " & _
    "           ([User_Name] " & _
    "           ,[DataCut_ID] " & _
    "           ,[Description] " & _
    "           ,[Region] " & _
    "           ,[Local_Authority] " & _
    "           ,[UPIN] " & _
    "           ,[Provider_Name] " & _
    "           ,[Case_Value] " & _
    "           ,[Act_Value] " & _
    "           ,[Note_User] " & _
    "           ,[Note_Date] " & _
    "           ,[Note] " & _
    "           ,[Attachment_Count],[Approved_Value]) " & _
    "     VALUES " & _
    "           (@User_Name " & _
    "           ,@DataCut_ID " & _
    "           ,@Description " & _
    "           ,@Region " & _
    "           ,@Local_Authority " & _
    "           ,@UPIN " & _
    "           ,@Provider_Name " & _
    "           ,@Case_Value " & _
    "           ,@Act_Value " & _
    "           ,@Note_User " & _
    "           ,@Note_Date " & _
    "           ,@Note " & _
    "           ,@Attachment_Count,@Approved_Value) "

                                Dim cnn As SqlConnection
                                cnn = New SqlConnection(StoreConnect)
                                cnn.Open()
                                Dim myCommand As New SqlCommand(sql, cnn)

                                With myCommand.Parameters
                                    .AddWithValue("@User_Name", CurrentUser.Name)
                                    .AddWithValue("@DataCut_ID", TemplateGrid.Item(TemplateGrid.Columns("DataCut_ID").Index, TemplateGrid.CurrentRow.Index).Value.ToString)
                                    .AddWithValue("@Description", TemplateGrid.Item(TemplateGrid.Columns("Case").Index, TemplateGrid.CurrentRow.Index).Value.ToString)
                                    .AddWithValue("@Region", DetailGrid.Item(DetailGrid.Columns("Region").Index, r).Value.ToString)
                                    .AddWithValue("@Local_Authority", DetailGrid.Item(DetailGrid.Columns("Local Authority").Index, r).Value.ToString)
                                    .AddWithValue("@UPIN", DetailGrid.Item(DetailGrid.Columns("UPIN").Index, r).Value)
                                    .AddWithValue("@Provider_Name", DetailGrid.Item(DetailGrid.Columns("Provider").Index, r).Value.ToString)
                                    .AddWithValue("@Case_Value", DetailGrid.Item(c, r).Value)
                                    Try
                                        .AddWithValue("@Act_Value", DetailGrid.Item(DetailGrid.Columns(DetailGrid.Columns(c).Name & " Actual").Index, r).Value)
                                    Catch ex As Exception
                                        'the field does not exist
                                        .AddWithValue("@Act_Value", DetailGrid.Item(DetailGrid.Columns("Products Actual").Index, r).Value)
                                    End Try

                                    If rn = -1 Then
                                        .AddWithValue("@Note_User", DBNull.Value)
                                        .AddWithValue("@Note_Date", DBNull.Value)
                                        .AddWithValue("@Note", DBNull.Value)
                                    Else
                                        .AddWithValue("@Note_User", NoteView(rn).Item("User_Name").ToString)
                                        .AddWithValue("@Note_Date", NoteView(rn).Item("TheDate"))
                                        .AddWithValue("@Note", NoteView(rn).Item("Note").ToString)
                                    End If

                                    .AddWithValue("@Attachment_Count", 0)
                                    .AddWithValue("@Approved_Value", DetailGrid.Item(DetailGrid.Columns("Approved").Index, r).Value)
                                End With

                                myCommand.ExecuteNonQuery()
                                cnn.Close()
                            Next

                        End If

                    End If

                End If
            Next

        Next



    End Sub

    Private Sub FullScreenButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FullScreenButton.Click

        If IsFullScreen = False Then
            TemplatePanel.Visible = False
            IsFullScreen = True
        Else
            TemplatePanel.Visible = True
            IsFullScreen = False
        End If

    End Sub

    Private Sub CasesForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ShowLoadingForm()

        If CurrentUser.Level = 0 Then
            DetailsSaveButton.Enabled = False
        Else
            DetailsSaveButton.Enabled = True
        End If

        LoadTemplateGrid()
        LoadTheView()
        LoadRegionBox()
        LoadProviderBox()
        GetDefaults()
        SetDefaults()



        'set security

        If CurrentUser.Level = 0 Then
            DetailsSaveButton.Enabled = False

        End If

        FireChanges = True

        HideLoadingForm()

    End Sub

    Private Sub LoadTemplateGrid()

        Dim sql As String
        Dim TheData As DataTable

        Job("Loading Case Templates...")

        sql = "select ids.DataCut_ID, Description as [Case], Notes, Budget_ID, Table_Name_Act, DataCut_ID_Act from Case_Templates_IDs ids, .Case_Templates c where c.DataCut_ID = ids.DataCut_ID"

        If CurrentUser.Level <> 5 Then
            sql = sql & " and (invisible is null or invisible = 0)"
        End If

        TheData = LoadDataTable(sql)
        TemplateGrid.DataSource = TheData
        'TemplateGrid.Columns("DataCut_ID").Visible = False
        TemplateGrid.Refresh()
        TheData = Nothing

        'hide columns
        TemplateGrid.Columns("DataCut_ID").Visible = False
        TemplateGrid.Columns("Notes").Visible = False
        TemplateGrid.Columns("Budget_ID").Visible = False


    End Sub

    Private Sub LoadTheView()
        'for each Type of data out there, load it into the viewer
        Dim TableView As DataView
        Dim CaseView As DataView
        Dim ANode As TreeNode

        TableView = LoadDataView("select * from dbo.Modelling_TableList where Table_Name in (select distinct Table_Name from Case_Templates)")

        Dim arow As DataRowView
        Dim crow As DataRowView

        TheTreeView.Nodes.Clear()
        For Each arow In TableView
            ANode = TheTreeView.Nodes.Add(arow.Item("Table_Name").ToString, arow.Item("Table_Description").ToString, 3, 3)

            CaseView = LoadDataView("select ct.DataCut_ID, ct.Table_Name, cti.Description from Case_Templates ct, dbo.Case_Templates_IDs cti where ct.DataCut_ID = cti.DataCut_ID and ct.Table_Name = '" & arow.Item("Table_Name").ToString & "' and (invisible is null or invisible = 0) order by description")

            For Each crow In CaseView
                ANode.Nodes.Add(crow.Item("DataCut_ID").ToString, crow.Item("Description").ToString, 4, 4)
            Next

        Next

        If CurrentUser.Level = 5 Then
            'add a hidden one at the end
            ANode = TheTreeView.Nodes.Add("Hidden", "Hidden Cases", 3, 3)

            'Load the hidden ones
            CaseView = LoadDataView("Select DataCut_ID, Description from Case_Templates_IDs where Invisible = 1")
            For Each crow In CaseView
                ANode.Nodes.Add(crow.Item("DataCut_ID").ToString, crow.Item("Description").ToString, 4, 4)
            Next
        End If

    End Sub

    Private Sub LoadRegionBox()
        Job("Loading Regions...")
        RegionBox.DataSource = LoadDataView("Select '<All>' as Region_Code, '<All Regions>' as Region_Name UNION ALL Select * from Regions")
        RegionBox.DisplayMember = "Region_Name"
        RegionBox.ValueMember = "Region_Code"
        RegionBox.DropDownStyle = ComboBoxStyle.DropDownList
        RegionBox.Refresh()

    End Sub

    Private Sub LoadProviderBox()
        Job("Loading Providers...")

        If RegionBox.SelectedValue = "<All>" Then
            ProviderBox.DataSource = LoadDataView("Select 0 as UPIN, '<All Providers>' as Provider_Name UNION ALL select distinct UPIN, Provider_Name from all_provider_fields order by Provider_Name")
        Else
            ProviderBox.DataSource = LoadDataView("Select 0 as UPIN, '<All Providers>' as Provider_Name UNION ALL select distinct UPIN, Provider_Name from all_provider_fields where Region_Code = '" & RegionBox.SelectedValue.ToString & "' order by Provider_Name")
        End If

        ProviderBox.DisplayMember = "Provider_Name"
        ProviderBox.ValueMember = "UPIN"
        ProviderBox.DropDownStyle = ComboBoxStyle.DropDownList
        ProviderBox.Refresh()

    End Sub

    Private Sub FillDetailGrid()
        Dim BudgetSQLLine As String

        Job("Loading Data...")
        ShowLoadingForm()

        If TemplateGrid.Rows.Count = 0 Then Exit Sub
        If RegionBox.SelectedValue.ToString = "System.Data.DataRowView" Then Exit Sub

        Try
            DataGridViewAutoFilterTextBoxColumn.RemoveFilter(DetailGrid)
        Catch ex As Exception

        End Try

        Dim sql As String
        Dim Cases As String
        Dim Joins As String
        Dim DataCutID As String

        Try
            DataCutID = Trim(TemplateGrid.Item(TemplateGrid.Columns("DataCut_ID").Index, TemplateGrid.CurrentRow.Index).Value.ToString)
            InstructionBox.Text = TemplateGrid.Item(TemplateGrid.Columns("Case").Index, TemplateGrid.CurrentCell.RowIndex).Value.ToString & vbNewLine & vbNewLine & _
                                TemplateGrid.Item(TemplateGrid.Columns("Notes").Index, TemplateGrid.CurrentCell.RowIndex).Value.ToString
        Catch ex As Exception
            HideLoadingForm()
            Job("Ready")
            Exit Sub

        End Try

        Cases = CaseStatements()
        If CaseStatements() = "" Then Exit Sub
        Joins = JoinStatements()

        If Trim(TemplateGrid.Item(TemplateGrid.Columns("Budget_ID").Index, TemplateGrid.CurrentRow.Index).Value.ToString) <> "NA" Then
            BudgetSQLLine = "and p.UPIN in (select distinct UPIN from Provider_Funding_Streams where Budget_ID = '" & TemplateGrid.Item(TemplateGrid.Columns("Budget_ID").Index, TemplateGrid.CurrentRow.Index).Value.ToString & "') "
        Else
            BudgetSQLLine = " "
        End If

        Dim provselect As String

        If ProviderBox.SelectedValue = 0 Then
            provselect = ""
        Else
            provselect = "and UPIN = " & ProviderBox.SelectedValue
        End If

        If RegionBox.SelectedValue.ToString <> "<All>" Then

            sql = "select ca.CaseAtts, cn.CaseNotes, cast(null as image) as N, cast(null as image) as A, P.RID, P.UPIN as UPIN, P.Provider_Name as Provider, P.LA_Name as 'Local Authority', P.Region_Name as Region, a.DataCut_ID, a.Description, " & Cases & " " & _
                  "from dbo.Case_Templates_IDs a, dbo.Case_Templates b, (Select * from all_provider_fields where Region_Code = '" & RegionBox.SelectedValue.ToString & "' and Lead = 1 " & provselect & ") P " & _
                  Joins & _
                  " left outer join (select count(user_name) as casenotes, DataCut_ID, RID from Case_Notes group by DataCut_ID, RID) cn on (cn.DataCut_ID = '" & DataCutID & "' and cn.RID = p.RID) " & _
                  " left outer join (select count(filename) as caseatts, DataCut_ID, RID from Case_Attachments group by DataCut_ID, RID) ca on (ca.DataCut_ID = '" & DataCutID & "' and ca.RID = p.RID) " & _
                  "where(a.DataCut_ID = b.DataCut_ID) and a.DataCut_ID = '" & DataCutID & "' " & _
                  BudgetSQLLine & _
                  "Group by ca.CaseAtts, cn.CaseNotes, P.Rid, P.UPIN, P.Provider_Name, P.LA_Name, P.Region_Name, a.DataCut_ID, a.Description " & _
                  "Order by Provider, 'Local Authority', Region"

        Else

            sql = "select ca.CaseAtts, cn.CaseNotes, cast(null as image) as N, cast(null as image) as A, P.RID, P.UPIN as UPIN, P.Provider_Name as Provider, P.LA_Name as 'Local Authority', P.Region_Name as Region, a.DataCut_ID, a.Description, " & Cases & " " & _
                  "from dbo.Case_Templates_IDs a, dbo.Case_Templates b, (Select * from all_provider_fields where Lead = 1 " & provselect & ") P " & _
                  Joins & _
                  " left outer join (select count(user_name) as casenotes, DataCut_ID, RID from Case_Notes group by DataCut_ID, RID) cn on (cn.DataCut_ID = '" & DataCutID & "' and cn.RID = p.RID) " & _
                  " left outer join (select count(filename) as caseatts, DataCut_ID, RID from Case_Attachments group by DataCut_ID, RID) ca on (ca.DataCut_ID = '" & DataCutID & "' and ca.RID = p.RID) " & _
                  "where(a.DataCut_ID = b.DataCut_ID) and a.DataCut_ID = '" & DataCutID & "' " & _
                  BudgetSQLLine & _
                  "Group by ca.CaseAtts, cn.CaseNotes, P.Rid, P.UPIN, P.Provider_Name, P.LA_Name, P.Region_Name, a.DataCut_ID, a.Description " & _
                  "Order by Provider, 'Local Authority', Region"

        End If


        DetailGrid.AutoGenerateColumns = True
        DetailGrid.Columns.Clear()

        TheData.DataSource = LoadDataTable(sql)
        DetailGrid.DataSource = TheData

        Dim col As DataGridViewColumn

        For Each col In DetailGrid.Columns
            col.HeaderCell = New DataGridViewAutoFilterColumnHeaderCell(col.HeaderCell)

        Next

        DetailGrid.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText

        ' ReadOnlyAndHideColumns()
        Dim c As DataGridViewColumn

        For Each c In DetailGrid.Columns
            If c.Name.Contains("_Status") Then c.Visible = False
            If c.Name.Contains("DataCut_ID") Then c.Visible = False
            If c.Name.Contains("Region") Then c.Visible = False
            If c.Name.Contains("Description") Then c.Visible = False
            If c.Name.Contains("_Source") Then c.Visible = False
            If c.Name = "RID" Then c.Visible = False
            If c.Name = "CaseAtts" Then c.Visible = False
            If c.Name = "CaseNotes" Then c.Visible = False

            If c.Name = "RID" Then c.ReadOnly = True
            If c.Name = "UPIN" Then c.ReadOnly = True
            If c.Name = "Provider" Then c.ReadOnly = True
            If c.Name = "Local Authority" Then c.ReadOnly = True
            If c.Name.Contains("Actual") Then c.ReadOnly = True
            If c.Name = "Factors" Then c.ReadOnly = True
            If c.Name = "Approved" Then c.ReadOnly = True
        Next

        HideLoadingForm()

        Job("Ready")
    End Sub

    Private Function CaseStatements() As String

        Dim sql, TableName As String
        Dim TheData As DataTable
        Dim i, r As Integer
        Dim DataCutID As String
        Dim ActTableName As String

        DataCutID = TemplateGrid.Item(TemplateGrid.Columns("DataCut_ID").Index, TemplateGrid.CurrentRow.Index).Value.ToString

        sql = "Select * from Case_Templates_IDs as a, Case_Templates As b where a.DataCut_ID = b.DataCut_Id and a.DataCut_ID = '" & DataCutID & "'"
        TheData = LoadDataTable(sql)
        r = TheData.Rows.Count

        If r = 0 Then
            CaseStatements = ""
            Exit Function
        End If


        sql = ""

        For i = 0 To r - 1

            TableName = TheData.Rows(i).Item("Table_Name")
            ActTableName = TheData.Rows(i).Item("Table_Name_Act")

            sql = sql & "sum(AppTab.Value) as [Approved], "

            If TableName = "Modelling_Factors" Then
                'its just an odering thing... but other than that its exactly the same
                sql = sql & "sum(case b.Table_Name_Act when '" & ActTableName & "' then " & ActTableName & "_Act.Value else null end) as [" & Mid(ActTableName, 11, Len(ActTableName)) & " Actual], "

                sql = sql & "Max(" & TableName & "_Max.Status) as " & Mid(TableName, 11, Len(TableName)) & "_Status, "

                sql = sql & "sum(case b.Table_name when '" & TableName & "' then " & TableName & ".Value else null end) as " & Mid(TableName, 11, Len(TableName)) & ", "

                sql = sql & "Max(" & TableName & ".TheSource) as " & Mid(TableName, 11, Len(TableName)) & "_Source, "

            Else
                sql = sql & "sum(case b.Table_name when '" & TableName & "' then " & TableName & ".Value else null end) as " & Mid(TableName, 11, Len(TableName)) & ", "

                sql = sql & "Max(" & TableName & "_Max.Status) as " & Mid(TableName, 11, Len(TableName)) & "_Status, "

                sql = sql & "sum(case b.Table_Name_Act when '" & ActTableName & "' then " & ActTableName & "_Act.Value else null end) as [" & Mid(ActTableName, 11, Len(ActTableName)) & " Actual], "

                sql = sql & "Max(" & TableName & ".TheSource) as " & Mid(TableName, 11, Len(TableName)) & "_Source, "

            End If

            If TableName = "Modelling_Factors" Then
                IsFactors = True

                'sql = sql & "sum(case b.Table_name when '" & TableName & "' then " & TableName & ".Programme_Weighting else null end) as [Programme Weighting], "

                sql = sql & "sum(case b.Table_Name_Act when '" & ActTableName & "' then coalesce(" & TableName & ".Programme_Weighting," & ActTableName & "_Act.Programme_Weighting) else null end) as [Programme Weighting], "

                sql = sql & "Max(" & TableName & "_Max.Status) as [Programme Weighting_Status], "

                'sql = sql & "sum(case b.Table_name when '" & TableName & "' then " & TableName & ".Area_Cost else null end) as [Area Cost], "

                sql = sql & "sum(case b.Table_Name_Act when '" & ActTableName & "' then coalesce(" & TableName & ".Area_Cost," & ActTableName & "_Act.Area_Cost) else null end) as [Area Cost], "

                sql = sql & "Max(" & TableName & "_Max.Status) as [Area Cost_Status], "

                'sql = sql & "sum(case b.Table_name when '" & TableName & "' then " & TableName & ".Success_Factor else null end) as [Success Factor], "

                sql = sql & "sum(case b.Table_Name_Act when '" & ActTableName & "' then coalesce(" & TableName & ".Success_Factor," & ActTableName & "_Act.Success_Factor) else null end) as [Success Factor], "

                sql = sql & "Max(" & TableName & "_Max.Status) as [Success Factor_Status], "

                'sql = sql & "sum(case b.Table_name when '" & TableName & "' then " & TableName & ".Disadvantage_Factor else null end) as [Disadvantage Factor], "

                sql = sql & "sum(case b.Table_Name_Act when '" & ActTableName & "' then coalesce(" & TableName & ".Disadvantage_Factor," & ActTableName & "_Act.Disadvantage_Factor) else null end) as [Disadvantage Factor], "

                sql = sql & "Max(" & TableName & "_Max.Status) as [Disadvantage Factor_Status], "

                'sql = sql & "sum(case b.Table_name when '" & TableName & "' then " & TableName & ".Adjustment_Factor else null end) as [Adjustment Factor], "

                sql = sql & "sum(case b.Table_Name_Act when '" & ActTableName & "' then coalesce(" & TableName & ".Adjustment_Factor," & ActTableName & "_Act.Adjustment_Factor) else null end) as [Adjustment Factor], "

                sql = sql & "Max(" & TableName & "_Max.Status) as [Adjustment Factor_Status], "

                'sql = sql & "sum(case b.Table_name when '" & TableName & "' then " & TableName & ".Short_Course_Modifier else null end) as [Short Course Modifier], "

                sql = sql & "sum(case b.Table_Name_Act when '" & ActTableName & "' then coalesce(" & TableName & ".Short_Course_Modifier," & ActTableName & "_Act.Short_Course_Modifier) else null end) as [Short Course Modifier], "

                sql = sql & "Max(" & TableName & "_Max.Status) as [Short Course Modifier_Status], "

                'sql = sql & "sum(case b.Table_name when '" & TableName & "' then " & TableName & ".Long_Term_Residential_Factor else null end) as [Long Term Residential Factor], "

                sql = sql & "sum(case b.Table_Name_Act when '" & ActTableName & "' then coalesce(" & TableName & ".Long_Term_Residential_Factor," & ActTableName & "_Act.Long_Term_Residential_Factor) else null end) as [Long Term Residential Factor], "

                sql = sql & "Max(" & TableName & "_Max.Status) as [Long Term Residential Factor_Status], "

                'sql = sql & "sum(case b.Table_name when '" & TableName & "' then " & TableName & ".Care_Standards_Factor else null end) as [Care Standards Factor], "

                sql = sql & "sum(case b.Table_Name_Act when '" & ActTableName & "' then coalesce(" & TableName & ".Care_Standards_Factor," & ActTableName & "_Act.Care_Standards_Factor) else null end) as [Care Standards Factor], "

                sql = sql & "Max(" & TableName & "_Max.Status) as [Care Standards Factor_Status], "

            Else
                IsFactors = False
            End If

        Next

        'Trim last comma and space from Case Statement
        sql = Mid(sql, 1, Len(sql) - 2)

        CaseStatements = sql

        TheData = Nothing

    End Function

    Private Function JoinStatements() As String

        Dim sql, TableName, ActTableName, prodsql As String
        Dim TheData As DataTable
        Dim i, r As Integer
        Dim DataCutID As String
        Dim ActDataCutID As String
        Dim Scenario_ID, ProductNumber As String

        Dim ProdView As DataView

        DataCutID = TemplateGrid.Item(TemplateGrid.Columns("DataCut_ID").Index, TemplateGrid.CurrentRow.Index).Value.ToString

        sql = "Select * from Case_Templates_IDs as a, Case_Templates As b where a.DataCut_ID = b.DataCut_Id and a.DataCut_ID = '" & DataCutID & "'"
        TheData = LoadDataTable(sql)
        r = TheData.Rows.Count

        sql = ""
        prodsql = ""


        For i = 0 To r - 1

            TableName = TheData.Rows(i).Item("Table_Name")
            ActDataCutID = TheData.Rows(i).Item("DataCut_ID_Act")
            ActTableName = TheData.Rows(i).Item("Table_Name_Act")

            '    If TableName = "Modelling_Products" Then
            'TableName = "Modelling_Other"
            'End If

            'sql = sql & "left outer join (select * from " & TableName & " where DataCut_ID = '" & DataCutID & "') " & TableName & " on (" & TableName & ".RID = P.RID) "

            sql = sql & "left outer join (Select * from (Select RID as TheRID, DataCut_ID as TheDataCut_ID, Max(Source) as TheSource from " & TableName & _
                        " where DataCut_ID = '" & DataCutID & "' group by RID, DataCut_ID) a inner join " & TableName & " b on a.TheRID = b.RID and a.TheDataCut_ID = b.DataCut_ID " & _
                        "and a.TheSource = b.Source) " & TableName & " on (" & TableName & ".RID = P.RID) "

            sql = sql & "left outer join " & _
                                "(select m.RId, Status from " & TableName & " m, " & _
                                "(select Rid, max(Source) as Source from " & TableName & " where DataCut_ID = '" & DataCutID & "' group by Rid) MaxSource " & _
                                "where m.RID = MaxSource.RID and m.Source = MaxSource.Source and m.DataCut_ID = '" & DataCutID & "') " & TableName & "_Max " & _
                                "on (P.RID= " & TableName & "_Max.RID) "

            sql = sql & "left outer join " & _
                                "(Select * from " & TableName & " where DataCut_ID = '" & DataCutID & "' and Status = 'Approved') AppTab on (P.RID = AppTab.Rid) "

            If ActTableName = "Modelling_Products" Then
                prodsql = "select * from dbo.Modelling_Products where Scenario_ID + cast(Product_Number as nvarchar(max)) = '" & ActDataCutID & "'"

                ProdView = LoadDataView(prodsql)

                Scenario_ID = ProdView(0).Item("Scenario_ID")
                ProductNumber = ProdView(0).Item("Product_Number")

                prodsql = Modelling_ResultsForm.StageSQL(Scenario_ID, "", ProductNumber, "Product", False)

                sql = sql & "left outer join (select RID, [Final Result] as Value from (" & prodsql & ") productsql) " & ActTableName & "_Act on (P.RID = " & ActTableName & "_Act.RID) "
            Else
                sql = sql & "left outer join (select * from " & _
                                ActTableName & " where DataCut_ID = '" & ActDataCutID & "') " & ActTableName & "_Act on (P.RID = " & ActTableName & "_Act.RID) "
            End If

        Next

        JoinStatements = sql

        TheData = Nothing

    End Function

    Private Sub DetailGrid_CellValueChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DetailGrid.CellValueChanged

        If DetailGrid.Columns(e.ColumnIndex).Name = "A" Or DetailGrid.Columns(e.ColumnIndex).Name = "N" Then Exit Sub
        If DetailGrid.Columns(e.ColumnIndex).Name = "CaseNotes" Or DetailGrid.Columns(e.ColumnIndex).Name = "CaseAtts" Then Exit Sub

        ChangesMade = True

        With DetailGrid
            If .Columns(e.ColumnIndex).Name.Contains("_Status") Then Exit Sub

            'check to see if this is an approved case and then basically just change it to ChangedApp rather then just Changed

            If .Item(.Columns(.Columns(e.ColumnIndex).Name & "_Status").Index, e.RowIndex).Value.ToString = "Approved" Then

                .Item(.Columns(.Columns(e.ColumnIndex).Name & "_Status").Index, e.RowIndex).Value = "ChangedApp"
            Else
                If .Item(.Columns(.Columns(e.ColumnIndex).Name & "_Status").Index, e.RowIndex).Value.ToString = "ChangedApp" Then
                    .Item(.Columns(.Columns(e.ColumnIndex).Name & "_Status").Index, e.RowIndex).Value = "ChangedApp"
                Else
                    .Item(.Columns(.Columns(e.ColumnIndex).Name & "_Status").Index, e.RowIndex).Value = "Changed"
                End If
            End If

        End With

        Dim Programme_Weighting As String = ""
        Dim Area_Cost As String = ""
        Dim Success_Factor As String = ""
        Dim Disadvantage_Factor As String = ""
        Dim Adjustment_Factor As String = ""
        Dim Short_Course_Modifier As String = ""
        Dim Long_Term_Residential_Factor As String = ""
        Dim Care_Standards_Factor As String = ""
        Dim TheValue As Decimal
        Dim r As Integer

        If IsFactors Then
            If DetailGrid.Columns(e.ColumnIndex).Name = "Factors" Then Exit Sub

            'calculate the factor
            With DetailGrid
                r = e.RowIndex

                Programme_Weighting = .Item(.Columns("Programme Weighting").Index, r).Value.ToString
                Area_Cost = .Item(.Columns("Area Cost").Index, r).Value.ToString
                Success_Factor = .Item(.Columns("Success Factor").Index, r).Value.ToString
                Disadvantage_Factor = .Item(.Columns("Disadvantage Factor").Index, r).Value.ToString
                Adjustment_Factor = .Item(.Columns("Adjustment Factor").Index, r).Value.ToString
                Short_Course_Modifier = .Item(.Columns("Short Course Modifier").Index, r).Value.ToString
                Long_Term_Residential_Factor = .Item(.Columns("Long Term Residential Factor").Index, r).Value.ToString
                Care_Standards_Factor = .Item(.Columns("Care Standards Factor").Index, r).Value.ToString

                If Programme_Weighting.Length = 0 Then Programme_Weighting = 0
                If Area_Cost.Length = 0 Then Area_Cost = 0
                If Success_Factor.Length = 0 Then Success_Factor = 0
                If Disadvantage_Factor.Length = 0 Then Disadvantage_Factor = 0
                If Adjustment_Factor.Length = 0 Then Adjustment_Factor = 0
                If Short_Course_Modifier.Length = 0 Then Short_Course_Modifier = 0
                If Long_Term_Residential_Factor.Length = 0 Then Long_Term_Residential_Factor = 0
                If Care_Standards_Factor.Length = 0 Then Care_Standards_Factor = 0

                TheValue = Programme_Weighting * Area_Cost * Success_Factor * Disadvantage_Factor * Adjustment_Factor * Short_Course_Modifier * Long_Term_Residential_Factor * Care_Standards_Factor
                .Item(.Columns("Factors").Index, r).Value = TheValue
            End With
        End If
    End Sub

    Private Sub DetailsSaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DetailsSaveButton.Click

        If CurrentUser.Level = 0 Then Exit Sub

        If ChangesMade = False Then
            MessageBox.Show(Me, "You haven't made any changes that can be saved", "No Changes Saved", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else
            SaveData()
            'set the current selection as the default

            Dim sql As String
            Dim Aview As DataView

            sql = "select * from local_authorities where Region_Code = '" & RegionBox.SelectedValue.ToString & "'"

            Aview = LoadDataView(sql)

            If Aview.Count <> 0 Then
                sql = "Update Users set Region_Code = '" & RegionBox.SelectedValue.ToString & "', LA_Code = '" & Aview(0).Item("LA_Code").ToString & "' " & _
              "where User_name = '" & CurrentUser.Name & "'"

                RunNonQuery(sql)

                DefaultRegion = RegionBox.SelectedValue.ToString
            End If

        End If

    End Sub

    Private Sub SaveData()

        If CurrentUser.Level = 0 Then Exit Sub

        ShowLoadingForm()
        Job("Saving Data...")
        DataGridViewAutoFilterTextBoxColumn.RemoveFilter(DetailGrid)
        Dim Rid As String
        Dim DataCut_ID As String
        Dim Description As String
        Dim Status As String = "Draft"
        Dim Value As String
        Dim r, c As Integer
        Dim sql As String = ""
        Dim TableName As String
        Dim ShortName As String
        Dim Source As String

        Dim Programme_Weighting As String = ""
        Dim Area_Cost As String = ""
        Dim Success_Factor As String = ""
        Dim Disadvantage_Factor As String = ""
        Dim Adjustment_Factor As String = ""
        Dim Short_Course_Modifier As String = ""
        Dim Long_Term_Residential_Factor As String = ""
        Dim Care_Standards_Factor As String = ""


        'Loop through Rows
        For r = 0 To DetailGrid.Rows.Count - 1


            Rid = DetailGrid.Item(DetailGrid.Columns("RID").Index, r).Value.ToString
            DataCut_ID = Trim(DetailGrid.Item(DetailGrid.Columns("DataCut_ID").Index, r).Value.ToString)
            Description = Trim(DetailGrid.Item(DetailGrid.Columns("Description").Index, r).Value.ToString)

            'Loop through Columns to build insert statments
            For c = 0 To DetailGrid.Columns.Count - 1

                If DetailGrid.Columns(c).Name = "Factors" Or DetailGrid.Columns(c).Name = "Learners" Or DetailGrid.Columns(c).Name = "Other" Or DetailGrid.Columns(c).Name = "Rates" Or DetailGrid.Columns(c).Name = "SLNs" Or DetailGrid.Columns(c).Name = "Funding" Then


                    TableName = DetailGrid.Columns(c).HeaderText
                    ShortName = DetailGrid.Columns(c).HeaderText

                    If Not TableName.Contains("_Status") Then

                        With DetailGrid
                            'Only save it if the Status has changed
                            Source = .Item(.Columns(ShortName & "_Source").Index, r).Value.ToString

                            If .Item(.Columns(TableName & "_Status").Index, r).Value.ToString.Contains("Changed") Then

                                TableName = "Modelling_" & TableName

                                If .Item(c, r).Value.ToString.Length <> 0 Then
                                    Value = .Item(c, r).Value.ToString
                                Else
                                    Value = 0
                                End If

                                If IsFactors Then
                                    Programme_Weighting = .Item(.Columns("Programme Weighting").Index, r).Value.ToString
                                    Area_Cost = .Item(.Columns("Area Cost").Index, r).Value.ToString
                                    Success_Factor = .Item(.Columns("Success Factor").Index, r).Value.ToString
                                    Disadvantage_Factor = .Item(.Columns("Disadvantage Factor").Index, r).Value.ToString
                                    Adjustment_Factor = .Item(.Columns("Adjustment Factor").Index, r).Value.ToString
                                    Short_Course_Modifier = .Item(.Columns("Short Course Modifier").Index, r).Value.ToString
                                    Long_Term_Residential_Factor = .Item(.Columns("Long Term Residential Factor").Index, r).Value.ToString
                                    Care_Standards_Factor = .Item(.Columns("Care Standards Factor").Index, r).Value.ToString

                                    If Programme_Weighting.Length = 0 Then Programme_Weighting = 0
                                    If Area_Cost.Length = 0 Then Area_Cost = 0
                                    If Success_Factor.Length = 0 Then Success_Factor = 0
                                    If Disadvantage_Factor.Length = 0 Then Disadvantage_Factor = 0
                                    If Adjustment_Factor.Length = 0 Then Adjustment_Factor = 0
                                    If Short_Course_Modifier.Length = 0 Then Short_Course_Modifier = 0
                                    If Long_Term_Residential_Factor.Length = 0 Then Long_Term_Residential_Factor = 0
                                    If Care_Standards_Factor.Length = 0 Then Care_Standards_Factor = 0

                                End If

                                '   If Value <> 0 Then

                                Source = "Case" & GetCaseNumber(Rid, DataCut_ID, TableName) + 1

                                If TableName = "Modelling_Factors" Then

                                    sql = "Insert into " & TableName & " Values('" & DataCut_ID & "', '" & Rid & "', '" & Source & "', 'Draft'" & _
                                   ", '" & Mid(Description, 1, 50) & "', " & Programme_Weighting & ", " & Area_Cost & ", " & Success_Factor & ", " & Disadvantage_Factor & ", " & Adjustment_Factor & ", " & Short_Course_Modifier & ", " & Long_Term_Residential_Factor & ", " & Care_Standards_Factor & ", '" & Value & "') "

                                Else

                                    sql = "Insert into " & TableName & " Values('" & DataCut_ID & "', '" & Rid & "', '" & Source & "', 'Draft'" & _
                                ", '" & Mid(Description, 1, 50) & "', '" & Value & "') "

                                End If

                                '   Clipboard.Clear()
                                '  Clipboard.SetText(sql)
                                Dim msg As String

                                msg = RunNonQuery(sql)

                                If msg <> "" Then
                                    MessageBox.Show(msg)
                                    '  MessageBox.Show(sql)
                                End If
                                '  End If

                            End If

                            'Handle any Status updates like Approved and Reject
                            If .Item(.Columns(ShortName & "_Status").Index, r).Value.ToString.Contains("Approved") Or .Item(.Columns(ShortName & "_Status").Index, r).Value.ToString.Contains("Rejected") Then

                                TableName = "Modelling_" & ShortName

                                If .Item(.Columns(ShortName & "_Status").Index, r).Value.ToString.Contains("Approved") Then
                                    Status = "Approved"
                                Else
                                    Status = "Rejected"
                                End If


                                'set all approved ones to Draft to make sure you always only have 1 approved
                                If Status = "Approved" Then
                                    sql = "update " & TableName & " set Status = 'Draft' where DataCut_ID = '" & DataCut_ID & "' and RID = '" & Rid & "' and Status = 'Approved'"
                                    RunNonQuery(sql)
                                End If

                                sql = "update " & TableName & " set Status = '" & Status & "' where DataCut_ID = '" & DataCut_ID & "' and RID = '" & Rid & "' and Source = '" & Source & "'"
                                RunNonQuery(sql)

                            End If

                        End With

                    End If

                End If
            Next c

        Next r

        ChangesMade = False

        HideLoadingForm()

        MessageBox.Show(Me, "Your changes have been saved successfully", "Save Successful", MessageBoxButtons.OK, MsgBoxStyle.Information)

    End Sub

    Private Function GetCaseNumber(ByVal RID As String, ByVal DataCut_ID As String, ByVal TableName As String) As Integer

        Dim sql As String
        Dim TheData As DataTable

        sql = "Select * from " & TableName & " where RID = '" & RID & "' and DataCut_ID = '" & DataCut_ID & "' and Source Like 'Case%'"
        TheData = LoadDataTable(sql)

        GetCaseNumber = TheData.Rows.Count

        TheData = Nothing

    End Function

    Private Sub CasesForm_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing

        If CurrentUser.Level = 0 Then Exit Sub

        If ChangesMade = True Then

            If MessageBox.Show(Me, "Do you want to save your business cass changes? If so, please click yes to save your changes", "Do you want to save Business Case changes?", MessageBoxButtons.YesNo, MsgBoxStyle.Exclamation) = Windows.Forms.DialogResult.Yes Then
                SaveData()
            Else
                ChangesMade = False
            End If

        End If

    End Sub

    Private Sub GetDefaults()

        Dim sql As String
        Dim TheData As DataTable

        sql = "select Region_Code from Users where User_Name = '" & CurrentUser.Name & "'"

        '    Clipboard.Clear()
        '   Clipboard.SetText(sql)

        TheData = LoadDataTable(sql)

        If TheData.Rows.Count > 0 Then
            DefaultRegion = TheData.Rows(0).Item("Region_Code").ToString
        Else
            DefaultRegion = ""
        End If

        TheData = Nothing

    End Sub

    Private Sub SetDefaults()

        If DefaultRegion = "" Then Exit Sub

        RegionBox.SelectedValue = DefaultRegion
        RegionBox.Refresh()

    End Sub

    Private Sub DetailGrid_CellValidating(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) Handles DetailGrid.CellValidating

       

    End Sub

    Private Sub DetailGrid_CellValidated(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DetailGrid.CellValidated

        If DetailGrid.Columns(e.ColumnIndex).HeaderText = "Factors" Then

            If DetailGrid.Item(e.ColumnIndex, e.RowIndex).Value.ToString = "" Then Exit Sub

            DetailGrid.Item(e.ColumnIndex, e.RowIndex).Value = Round(DetailGrid.Item(e.ColumnIndex, e.RowIndex).Value, 3)

        End If

    End Sub

    Private Sub DetailGrid_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DetailGrid.CellFormatting

        If e.RowIndex = -1 Then Exit Sub

        If DetailGrid.Columns(e.ColumnIndex).Name = "A" Or DetailGrid.Columns(e.ColumnIndex).Name = "N" Then
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        Else
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        End If



        If DetailGrid.Columns(e.ColumnIndex).Name.Contains("Funding") Then
            DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "£#,#"
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            CellFormat = DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format

            DetailGrid.Columns("Approved").DefaultCellStyle = DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle

        End If

        If DetailGrid.Columns(e.ColumnIndex).Name.Contains("Rates") Then
            DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "£#,#"
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            CellFormat = DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format

            DetailGrid.Columns("Approved").DefaultCellStyle = DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle

        End If

        If DetailGrid.Columns(e.ColumnIndex).Name.Contains("Factors") Then
            DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#.000"
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            CellFormat = DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format

            DetailGrid.Columns("Approved").DefaultCellStyle = DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle

        End If

        If DetailGrid.Columns(e.ColumnIndex).Name.Contains("SLNs") Then
            DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#.00"
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            CellFormat = DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format

            DetailGrid.Columns("Approved").DefaultCellStyle = DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle

        End If

        If DetailGrid.Columns(e.ColumnIndex).Name.Contains("Other") Then

            If Trim(TemplateGrid.Item(TemplateGrid.Columns("DataCut_ID").Index, TemplateGrid.CurrentRow.Index).Value.ToString) = "CASEIDNUMBER00000010720110714230844" Or _
                Trim(TemplateGrid.Item(TemplateGrid.Columns("DataCut_ID").Index, TemplateGrid.CurrentRow.Index).Value.ToString) = "CASEIDNUMBER00000010720110714480632" Then
                DetailGrid.Columns("Approved").DefaultCellStyle.Format = "n3"
                DetailGrid.Columns("Other").DefaultCellStyle.Format = "n3"
                DetailGrid.Columns("Other Actual").DefaultCellStyle.Format = "n3"
            Else
                DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#.00"
                e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                CellFormat = DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format
                DetailGrid.Columns("Approved").DefaultCellStyle = DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle
            End If

        End If

            If DetailGrid.Columns(e.ColumnIndex).Name.Contains("Learners") Then
                DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#"
                e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                CellFormat = DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format

                DetailGrid.Columns("Approved").DefaultCellStyle = DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle

            End If

            If DetailGrid.Columns(e.ColumnIndex).Name.Contains("Products") Then
                If CellFormat <> "" Then
                    DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle.Format = CellFormat

                End If

                e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

                DetailGrid.Columns("Approved").DefaultCellStyle = DetailGrid.Columns(e.ColumnIndex).DefaultCellStyle

            End If

            If DetailGrid.Columns(e.ColumnIndex).Name.Contains("Approved") Then
                e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            End If


            Dim Status As String
            Dim StatusColumn As String

            If DetailGrid.Columns(e.ColumnIndex).Name.Contains("Actual") Then
                e.CellStyle.BackColor = Color.LightGray
            End If


            StatusColumn = DetailGrid.Columns(e.ColumnIndex).HeaderText



            If StatusColumn = "Factors" Or StatusColumn = "Learners" Or StatusColumn = "Other" Or StatusColumn = "Rates" Or StatusColumn = "SLNs" Or StatusColumn = "Funding" Then

                If CurrentUser.Level = 0 Then
                    DetailGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = True
                Else
                    If StatusColumn = "Factors" Then
                        DetailGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = True
                    Else
                        DetailGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = False
                    End If

                End If


                StatusColumn = StatusColumn & "_Status"
                Status = Trim(DetailGrid.Item(DetailGrid.Columns(StatusColumn).Index, e.RowIndex).Value.ToString)

                If Status.Contains("Rejected") Then
                    e.CellStyle.BackColor = Color.PaleVioletRed
                End If

                If Status.Contains("Approved") Then
                    e.CellStyle.BackColor = Color.LightGreen
                    ' DetailGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = True

                End If

                If Status = "Changed" Then
                    e.CellStyle.BackColor = Color.LightBlue
                End If

                If Status = "ChangedApp" Then
                    e.CellStyle.BackColor = Color.MediumPurple
                End If

            End If

            StatusColumn = DetailGrid.Columns(e.ColumnIndex).HeaderText

            If IsFactors Then

                Select Case StatusColumn
                    Case "Programme Weighting", "Area Cost", "Success Factor", "Disadvantage Factor", "Adjustment Factor", "Short Course Modifier", "Long Term Residential Factor", "Care Standards Factor"
                        If CurrentUser.Level = 0 Then
                            DetailGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = True
                        Else
                            DetailGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = False
                        End If



                        StatusColumn = StatusColumn & "_Status"
                        Status = Trim(DetailGrid.Item(DetailGrid.Columns(StatusColumn).Index, e.RowIndex).Value.ToString)

                        If Status.Contains("Rejected") Then
                            e.CellStyle.BackColor = Color.PaleVioletRed
                        End If

                        If Status.Contains("Approved") Then
                            e.CellStyle.BackColor = Color.LightGreen
                            DetailGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = True

                        End If

                        If Status = "Changed" Then
                            e.CellStyle.BackColor = Color.LightBlue
                        End If
                End Select

            End If

            'lets draw the pics
            If DetailGrid.Columns(e.ColumnIndex).Name = "N" Then
                Try
                    If DetailGrid.Item(DetailGrid.Columns("CaseNotes").Index, e.RowIndex).Value > 0 Then
                        e.Value = TheImages.Images(2)
                    Else
                        e.Value = TheImages.Images(0)
                    End If
                Catch ex As Exception
                    e.Value = TheImages.Images(0)
                End Try

            End If

            If DetailGrid.Columns(e.ColumnIndex).Name = "A" Then
                Try
                    If DetailGrid.Item(DetailGrid.Columns("CaseAtts").Index, e.RowIndex).Value > 0 Then
                        e.Value = TheImages.Images(1)
                    Else
                        e.Value = TheImages.Images(0)
                    End If
                Catch ex As Exception
                    e.Value = TheImages.Images(0)
                End Try

            End If

    End Sub



    Private Sub TemplateGrid_CurrentCellChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TemplateGrid.CurrentCellChanged

        'Trap a null reference
        ' Try
        ' If RegionBox.SelectedValue.ToString.Length = 0 Then Exit Sub
        ' Catch ex As Exception
        ' Exit Sub

        'End Try

        If FireChanges = False Then Exit Sub

        If ChangesMade = True Then

            If MessageBox.Show(Me, "Do you want to save your business cass changes? If so, please click yes to save your changes", "Do you want to save Business Case changes?", MessageBoxButtons.YesNo, MsgBoxStyle.Exclamation) = Windows.Forms.DialogResult.Yes Then
                SaveData()
            Else
                ChangesMade = False

            End If

        End If


        FillDetailGrid()

    End Sub

    Private Sub CasesForm_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Leave

        FireChanges = False

    End Sub

    Private Sub RegionBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RegionBox.SelectedIndexChanged

        If FireChanges = False Then Exit Sub

        LoadProviderBox()

        '   If ChangesMade = True Then

        ' If MessageBox.Show(Me, "Do you want to save your business cass changes? If so, please click yes to save your changes", "Do you want to save Business Case changes?", MessageBoxButtons.YesNo, MsgBoxStyle.Exclamation) = Windows.Forms.DialogResult.Yes Then
        ' SaveData()
        ' ShowLoadingForm()
        ' FillDetailGrid()
        ' HideLoadingForm()
        ' Else
        ' ChangesMade = False
        ' ShowLoadingForm()
        ' FillDetailGrid()
        ' HideLoadingForm()
        ' End If

        '        Else
        '        ShowLoadingForm()
        '        FillDetailGrid()
        '       HideLoadingForm()
        '      End If

    End Sub

    Private Sub CasesForm_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Enter

        FireChanges = True

    End Sub

    Private Sub DetailGrid_CellContextMenuStripNeeded(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellContextMenuStripNeededEventArgs) Handles DetailGrid.CellContextMenuStripNeeded


        ShowMenu = False

        If e.RowIndex = -1 Then
            Exit Sub
        End If

        If e.ColumnIndex = -1 Then
            Exit Sub
        End If

        Dim StatusColumn As String

        StatusColumn = DetailGrid.Columns(e.ColumnIndex).HeaderText

        If StatusColumn = "Factors" Or StatusColumn = "Learners" Or StatusColumn = "Other" Or StatusColumn = "Rates" Or StatusColumn = "SLNs" Or StatusColumn = "Funding" Then
            ShowMenu = True

            DetailGrid.CurrentCell = DetailGrid.Item(e.ColumnIndex, e.RowIndex)
            BuildMenuItems()

        End If

        If IsFactors Then
            Select Case StatusColumn
                Case "Programme Weighting", "Area Cost", "Success Factor", "Disadvantage Factor", "Adjustment Factor", "Short Course Modifier", "Long Term Residential Factor", "Care Standards Factor"
                    ShowMenu = True

                    DetailGrid.CurrentCell = DetailGrid.Item(e.ColumnIndex, e.RowIndex)
                    BuildMenuItems()
            End Select
        End If

        If CurrentUser.Level < 4 Then
            ApproveCaseToolStripMenuItem.Visible = False
            RejectCaseToolStripMenuItem.Visible = False
            RejectAllCasesToolStripMenuItem.Visible = False
        Else
            ApproveCaseToolStripMenuItem.Visible = True
            RejectCaseToolStripMenuItem.Visible = True
            RejectAllCasesToolStripMenuItem.Visible = True
        End If

        If StatusColumn = "A" Or StatusColumn = "N" Then
            ShowMenu = True

            DetailGrid.CurrentCell = DetailGrid.Item(e.ColumnIndex, e.RowIndex)
            'BuildMenuItems()
            ApproveCaseToolStripMenuItem.Visible = False
            RejectCaseToolStripMenuItem.Visible = False
            VersionMenu.Visible = False
        Else
            VersionMenu.Visible = True
        End If



    End Sub

    Private Sub DetailGrid_DataError(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles DetailGrid.DataError

        MessageBox.Show(Me, "You can only enter numbers in the business case section - you cannot enter text", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

    End Sub

    Private Sub BuildMenuItems()

        Dim sql As String
        Dim Rid As String
        Dim DataCutID As String
        Dim TableName As String
        Dim TheData As DataTable
        Dim i As Integer
        Dim MenuText As String
        Dim ColName As String = ""

        Rid = DetailGrid.Item(DetailGrid.Columns("RID").Index, DetailGrid.CurrentCell.RowIndex).Value
        DataCutID = DetailGrid.Item(DetailGrid.Columns("DataCut_ID").Index, DetailGrid.CurrentCell.RowIndex).Value

        If IsFactors Then
            TableName = "Modelling_Factors"
        Else
            TableName = "Modelling_" & DetailGrid.Columns(DetailGrid.CurrentCell.ColumnIndex).HeaderText
        End If


        sql = "Select * from " & TableName & " where RID = '" & Rid & "' and DataCut_ID = '" & DataCutID & "'"

        '  Clipboard.Clear()
        ' Clipboard.SetText(sql)

        TheData = LoadDataTable(sql)

        VersionMenu.DropDownItems.Clear()

        If TheData.Rows.Count = 0 Then
            'but wait... load the actuals anyway for factors
            If IsFactors Then
                'add another line for the actual
                sql = "Select * from Modelling_Factors where RID = '" & Rid & "' and DataCut_ID = '" & TemplateGrid.Item(TemplateGrid.Columns("DataCut_ID_Act").Index, TemplateGrid.CurrentCell.RowIndex).Value & "'"
                TheData = LoadDataTable(sql)

                Select Case DetailGrid.Columns(DetailGrid.CurrentCell.ColumnIndex).HeaderText
                    Case "Programme Weighting"
                        ColName = "Programme_Weighting"
                    Case "Area Cost"
                        ColName = "Area_Cost"
                    Case "Success Factor"
                        ColName = "Success_Factor"
                    Case "Disadvantage Factor"
                        ColName = "Disadvantage_Factor"
                    Case "Adjustment Factor"
                        ColName = "Adjustment_Factor"
                    Case "Short Course Modifier"
                        ColName = "Short_Course_Modifier"
                    Case "Long Term Residential Factor"
                        ColName = "Long_Term_Residential_Factor"
                    Case "Care Standards Factor"
                        ColName = "Care_Standards_Factor"
                    Case Else
                        ColName = "Value"
                End Select

                If TheData.Rows.Count <> 0 Then
                    MenuText = "Actual - " & TheData(0).Item(ColName)

                    Dim mySubMenuItem As New ToolStripMenuItem
                    mySubMenuItem.Name = MenuText
                    mySubMenuItem.Text = MenuText
                    RemoveHandler (mySubMenuItem.Click), AddressOf TheVersionItemMenuClick
                    AddHandler (mySubMenuItem.Click), AddressOf TheVersionItemMenuClick
                    mySubMenuItem.Enabled = True

                    VersionMenu.DropDownItems.Add(mySubMenuItem)
                End If

            End If

            VersionMenu.Enabled = True


            Exit Sub

        End If

        VersionMenu.Enabled = True

        For i = 0 To TheData.Rows.Count - 1

            If IsFactors Then
                Select Case DetailGrid.Columns(DetailGrid.CurrentCell.ColumnIndex).HeaderText
                    Case "Programme Weighting"
                        ColName = "Programme_Weighting"
                    Case "Area Cost"
                        ColName = "Area_Cost"
                    Case "Success Factor"
                        ColName = "Success_Factor"
                    Case "Disadvantage Factor"
                        ColName = "Disadvantage_Factor"
                    Case "Adjustment Factor"
                        ColName = "Adjustment_Factor"
                    Case "Short Course Modifier"
                        ColName = "Short_Course_Modifier"
                    Case "Long Term Residential Factor"
                        ColName = "Long_Term_Residential_Factor"
                    Case "Care Standards Factor"
                        ColName = "Care_Standards_Factor"
                    Case Else
                        ColName = "Value"
                End Select

            Else
                ColName = "Value"
            End If

            MenuText = "Version " & Mid(TheData(i).Item("Source"), 5, Len(TheData(i).Item("Source"))) & " - " & TheData(i).Item(ColName) & " (" & TheData(i).Item("Status") & ")"

            Dim mySubMenuItem As New ToolStripMenuItem
            mySubMenuItem.Name = MenuText
            mySubMenuItem.Text = MenuText
            RemoveHandler (mySubMenuItem.Click), AddressOf TheVersionItemMenuClick
            AddHandler (mySubMenuItem.Click), AddressOf TheVersionItemMenuClick
            mySubMenuItem.Enabled = True

            VersionMenu.DropDownItems.Add(mySubMenuItem)

        Next

        If IsFactors Then
            'add another line for the actual
            sql = "Select * from Modelling_Factors where RID = '" & Rid & "' and DataCut_ID = '" & TemplateGrid.Item(TemplateGrid.Columns("DataCut_ID_Act").Index, TemplateGrid.CurrentRow.Index).Value & "'"
            TheData = LoadDataTable(sql)

            Select Case DetailGrid.Columns(DetailGrid.CurrentCell.ColumnIndex).HeaderText
                Case "Programme Weighting"
                    ColName = "Programme_Weighting"
                Case "Area Cost"
                    ColName = "Area_Cost"
                Case "Success Factor"
                    ColName = "Success_Factor"
                Case "Disadvantage Factor"
                    ColName = "Disadvantage_Factor"
                Case "Adjustment Factor"
                    ColName = "Adjustment_Factor"
                Case "Short Course Modifier"
                    ColName = "Short_Course_Modifier"
                Case "Long Term Residential Factor"
                    ColName = "Long_Term_Residential_Factor"
                Case "Care Standards Factor"
                    ColName = "Care_Standards_Factor"
                Case Else
                    ColName = "Value"
            End Select

            If TheData.Rows.Count <> 0 Then
                MenuText = "Actual - " & TheData(0).Item(ColName)

                Dim mySubMenuItem As New ToolStripMenuItem
                mySubMenuItem.Name = MenuText
                mySubMenuItem.Text = MenuText
                RemoveHandler (mySubMenuItem.Click), AddressOf TheVersionItemMenuClick
                AddHandler (mySubMenuItem.Click), AddressOf TheVersionItemMenuClick
                mySubMenuItem.Enabled = True

                VersionMenu.DropDownItems.Add(mySubMenuItem)
            End If

        End If

    End Sub


    Private Sub TheVersionItemMenuClick(ByVal sender As Object, ByVal e As EventArgs)
        'do what you need to do with the version selected here
    End Sub




    Private Sub ToolStripTextBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub GridMenu_Opening(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles GridMenu.Opening
        e.Cancel = Not ShowMenu
    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ShowLoadingForm()

        LoadTemplateGrid()

        LoadTheView()

        HideLoadingForm()

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If DetailGrid.RowCount = 0 Then Exit Sub
        Try
            DataGridViewAutoFilterTextBoxColumn.RemoveFilter(DetailGrid)
        Catch ex As Exception

        End Try

    End Sub



    Private Sub DetailGrid_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DetailGrid.KeyUp

        'only run the following command(s) on editable cells
        If DetailGrid.CurrentCell.ReadOnly = True Then Exit Sub

        If e.KeyValue = 46 Then
            'delete key
            DetailGrid.CurrentCell.Value = DBNull.Value
        End If
    End Sub

    Private Sub ApproveCaseToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ApproveCaseToolStripMenuItem.Click
        'Approve the business case

        Try
            With DetailGrid
                If .Item(.Columns(.Columns(.CurrentCell.ColumnIndex).Name & "_Status").Index, .CurrentCell.RowIndex).Value = "Changed" Then
                    .Item(.Columns(.Columns(.CurrentCell.ColumnIndex).Name & "_Status").Index, .CurrentCell.RowIndex).Value = "Changed Approved"
                Else
                    .Item(.Columns(.Columns(.CurrentCell.ColumnIndex).Name & "_Status").Index, .CurrentCell.RowIndex).Value = "Approved"
                End If

                If IsFactors Then
                    'approve the entire row
                    Dim TheCell As DataGridViewCell
                    Dim ColName As String = ""

                    For Each TheCell In DetailGrid.CurrentRow.Cells
                        Select Case .Columns(TheCell.ColumnIndex).Name
                            Case "Programme Weighting", "Area Cost", "Success Factor", "Disadvantage Factor", "Adjustment Factor", "Short Course Modifier", "Long Term Residential Factor", "Care Standards Factor", "Factors"
                                ColName = .Columns(TheCell.ColumnIndex).Name

                                If .Item(.Columns(ColName & "_Status").Index, .CurrentCell.RowIndex).Value = "Changed" Then
                                    .Item(.Columns(ColName & "_Status").Index, .CurrentCell.RowIndex).Value = "Changed Approved"
                                Else
                                    .Item(.Columns(ColName & "_Status").Index, .CurrentCell.RowIndex).Value = "Approved"
                                End If
                        End Select
                    Next

                End If
                .Refresh()
            End With
        Catch ex As Exception
            'trying to approve a dbnull case
        End Try
    End Sub

    Private Sub RejectCaseToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RejectCaseToolStripMenuItem.Click
        'Reject the business case
        Try
            With DetailGrid
                If .Item(.Columns(.Columns(.CurrentCell.ColumnIndex).Name & "_Status").Index, .CurrentCell.RowIndex).Value = "Changed" Then
                    .Item(.Columns(.Columns(.CurrentCell.ColumnIndex).Name & "_Status").Index, .CurrentCell.RowIndex).Value = "Changed Rejected"
                Else
                    .Item(.Columns(.Columns(.CurrentCell.ColumnIndex).Name & "_Status").Index, .CurrentCell.RowIndex).Value = "Rejected"
                End If

                If IsFactors Then
                    'reject the entire row
                    Dim TheCell As DataGridViewCell
                    Dim ColName As String = ""

                    For Each TheCell In DetailGrid.CurrentRow.Cells
                        Select Case .Columns(TheCell.ColumnIndex).Name
                            Case "Programme Weighting", "Area Cost", "Success Factor", "Disadvantage Factor", "Adjustment Factor", "Short Course Modifier", "Long Term Residential Factor", "Care Standards Factor", "Factors"
                                ColName = .Columns(TheCell.ColumnIndex).Name

                                If .Item(.Columns(ColName & "_Status").Index, .CurrentCell.RowIndex).Value = "Changed" Then
                                    .Item(.Columns(ColName & "_Status").Index, .CurrentCell.RowIndex).Value = "Changed Rejected"
                                Else
                                    .Item(.Columns(ColName & "_Status").Index, .CurrentCell.RowIndex).Value = "Rejected"
                                End If
                        End Select
                    Next

                End If
                .Refresh()
            End With
        Catch ex As Exception
            'trying to approve a dbnull case
        End Try

    End Sub

    Private Sub NotesMenu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NotesMenu.Click
        CaseNotesForm.MdiParent = MainForm
        CaseNotesForm.Dock = DockStyle.Fill

        CaseNotesForm.Show()
        CaseNotesForm.Focus()


    End Sub


    Private Sub DetailGrid_CellPainting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DetailGrid.CellPainting
        If e.ColumnIndex = -1 Then Exit Sub

        Dim pt As Point

        If e.RowIndex = -1 AndAlso DetailGrid.Columns(e.ColumnIndex).Name = "A" Then

            pt = e.CellBounds.Location

            pt.X = pt.X + ((e.CellBounds.Width - TheImages.Images(1).Width) / 2)
            pt.Y = pt.Y + ((e.CellBounds.Height - TheImages.Images(1).Height) / 2)

            e.Paint(e.CellBounds, DataGridViewPaintParts.All And Not DataGridViewPaintParts.ContentForeground)
            e.Graphics.DrawImage(TheImages.Images(1), pt)
            e.Handled = True
        End If

        If e.RowIndex = -1 AndAlso DetailGrid.Columns(e.ColumnIndex).Name = "N" Then

            pt = e.CellBounds.Location

            pt.X = pt.X + ((e.CellBounds.Width - TheImages.Images(2).Width) / 2)
            pt.Y = pt.Y + ((e.CellBounds.Height - TheImages.Images(2).Height) / 2)

            e.Paint(e.CellBounds, DataGridViewPaintParts.All And Not DataGridViewPaintParts.ContentForeground)
            e.Graphics.DrawImage(TheImages.Images(2), pt)
            e.Handled = True
        End If
    End Sub

    
   
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        ReportsMenu.Show(Button3, New System.Drawing.Point(0, Button3.Height))

    End Sub

    Private Sub TheTreeView_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles TheTreeView.AfterSelect
        'select the one in the grid
        Dim r As Integer
        Dim selected As Integer = -1

        If TemplateGrid.RowCount <= 0 Then Exit Sub

        If Not TheTreeView.SelectedNode.Name.StartsWith("CASE") Then

            Exit Sub
        End If

        For r = 0 To TemplateGrid.RowCount - 1
            If TemplateGrid.Item(TemplateGrid.Columns("DataCut_ID").Index, r).Value = TheTreeView.SelectedNode.Name.ToString Then
                selected = r
            End If
        Next

        If selected <> -1 Then
            TemplateGrid.CurrentCell = TemplateGrid.Item(TemplateGrid.Columns("Case").Index, selected)
            DetailsLabel.Text = "Case Details:  " & TemplateGrid.Item(TemplateGrid.Columns("Case").Index, selected).Value
        End If
    End Sub

    Private Sub ProviderBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProviderBox.SelectedIndexChanged
        If FireChanges = False Then Exit Sub

        If ChangesMade = True Then

            If MessageBox.Show(Me, "Do you want to save your business cass changes? If so, please click yes to save your changes", "Do you want to save Business Case changes?", MessageBoxButtons.YesNo, MsgBoxStyle.Exclamation) = Windows.Forms.DialogResult.Yes Then
                SaveData()
                ShowLoadingForm()
                FillDetailGrid()
                HideLoadingForm()
            Else
                ChangesMade = False
                ShowLoadingForm()
                FillDetailGrid()
                HideLoadingForm()
            End If

        Else
            ShowLoadingForm()
            FillDetailGrid()
            HideLoadingForm()
        End If
    End Sub

    Private Sub AllCasesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AllCasesToolStripMenuItem.Click
        ShowLoadingForm()
        Job("Preparing the report, please wait...")

        PrepareCasesReport(0)

        Dim sql As String

        HideLoadingForm()

        Job("Ready")

        If DetailGrid.RowCount = 0 Then Exit Sub

        sql = "select * from cases_report where user_name = '" & CurrentUser.Name & "' order by Region, Local_Authority, UPIN, Note_Date"


        ShowReport("Cases Report", "Cases_Report.rdl", "TheStore", sql, "", "", "", "", "Report_Header", "All Cases", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)

    End Sub

    Private Sub ApprovedCasesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ApprovedCasesToolStripMenuItem.Click
        ShowLoadingForm()
        Job("Preparing the report, please wait...")

        PrepareCasesReport(1)

        Dim sql As String

        HideLoadingForm()

        Job("Ready")

        If DetailGrid.RowCount = 0 Then Exit Sub

        sql = "select * from cases_report where user_name = '" & CurrentUser.Name & "' order by Region, Local_Authority, UPIN, Note_Date"


        ShowReport("Cases Report", "Cases_Report.rdl", "TheStore", sql, "", "", "", "", "Report_Header", "Approved Cases", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)

    End Sub

    Private Sub RejecedCasesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RejecedCasesToolStripMenuItem.Click
        ShowLoadingForm()
        Job("Preparing the report, please wait...")

        PrepareCasesReport(2)

        Dim sql As String

        HideLoadingForm()

        Job("Ready")

        If DetailGrid.RowCount = 0 Then Exit Sub

        sql = "select * from cases_report where user_name = '" & CurrentUser.Name & "' order by Region, Local_Authority, UPIN, Note_Date"


        ShowReport("Cases Report", "Cases_Report.rdl", "TheStore", sql, "", "", "", "", "Report_Header", "Rejected Cases", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)

    End Sub

    Private Sub DraftCasesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DraftCasesToolStripMenuItem.Click
        ShowLoadingForm()
        Job("Preparing the report, please wait...")

        PrepareCasesReport(3)

        Dim sql As String

        HideLoadingForm()

        Job("Ready")

        If DetailGrid.RowCount = 0 Then Exit Sub

        sql = "select * from cases_report where user_name = '" & CurrentUser.Name & "' order by Region, Local_Authority, UPIN, Note_Date"


        ShowReport("Cases Report", "Cases_Report.rdl", "TheStore", sql, "", "", "", "", "Report_Header", "Draft Cases", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)

    End Sub

    Private Sub RejectAllCasesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RejectAllCasesToolStripMenuItem.Click
        'Reject the business case
        If MessageBox.Show("This will reject all cases including currently approved cases for this provider.  Are you sure you want to continue?  This cannot be undone.", "Reject all cases?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        Try
            With DetailGrid
                If .Item(.Columns(.Columns(.CurrentCell.ColumnIndex).Name & "_Status").Index, .CurrentCell.RowIndex).Value = "Changed" Then
                    .Item(.Columns(.Columns(.CurrentCell.ColumnIndex).Name & "_Status").Index, .CurrentCell.RowIndex).Value = "Changed Rejected"
                Else
                    .Item(.Columns(.Columns(.CurrentCell.ColumnIndex).Name & "_Status").Index, .CurrentCell.RowIndex).Value = "Rejected"
                End If

                If IsFactors Then
                    'reject the entire row
                    Dim TheCell As DataGridViewCell
                    Dim ColName As String = ""

                    For Each TheCell In DetailGrid.CurrentRow.Cells
                        Select Case .Columns(TheCell.ColumnIndex).Name
                            Case "Programme Weighting", "Area Cost", "Success Factor", "Disadvantage Factor", "Adjustment Factor", "Short Course Modifier", "Long Term Residential Factor", "Care Standards Factor", "Factors"
                                ColName = .Columns(TheCell.ColumnIndex).Name

                                If .Item(.Columns(ColName & "_Status").Index, .CurrentCell.RowIndex).Value = "Changed" Then
                                    .Item(.Columns(ColName & "_Status").Index, .CurrentCell.RowIndex).Value = "Changed Rejected"
                                Else
                                    .Item(.Columns(ColName & "_Status").Index, .CurrentCell.RowIndex).Value = "Rejected"
                                End If
                        End Select
                    Next

                End If
                .Refresh()

                Dim TableName As String = DetailGrid.Columns(.CurrentCell.ColumnIndex).HeaderText

                Dim DataCut_ID As String = Trim(DetailGrid.Item(DetailGrid.Columns("DataCut_ID").Index, .CurrentCell.RowIndex).Value.ToString)
                Dim Rid As String = DetailGrid.Item(DetailGrid.Columns("RID").Index, .CurrentCell.RowIndex).Value.ToString

                TableName = "Modelling_" & TableName

            
                Dim Status As String = "Rejected"
                Dim sql As String

                sql = "update " & TableName & " set Status = '" & Status & "' where DataCut_ID = '" & DataCut_ID & "' and RID = '" & Rid & "'"
                RunNonQuery(Sql)


            End With
        Catch ex As Exception
            'trying to approve a dbnull case
        End Try

    End Sub
End Class